package com.jtw.common.util;

import org.apache.poi.hssf.usermodel.*;

import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.Collection;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExcelUtil<T> {

    public void getWorkbook(String[] columnNames, String[] columnMethods, Collection<T> result, OutputStream out, String pattern) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet();

        //创建第1行，也就是输出表头
        HSSFRow row = sheet.createRow(0);
        HSSFCell cell;
        for (int i = 0; i < columnNames.length; i++) {
            //创建第i列
            cell = row.createCell(i);
            cell.setCellValue(new HSSFRichTextString(columnNames[i]));
        }

        int index = 0;
        for (T t : result) {
            row = sheet.createRow(++index);
            for (int j = 0; j < columnMethods.length; j++) {
                cell = row.createCell(j);
                Method getMethod = null;
                Object value = null;
                String getMethodName = "get" + columnMethods[j].substring(0, 1).toUpperCase() + columnMethods[j].substring(1);
                try {
                    getMethod = t.getClass().getMethod(getMethodName);
                    value = getMethod.invoke(t);
                } catch (Exception e) {
                    e.printStackTrace();
                }
                String textValue = null;
                if (value == null) {
                    textValue = "";
                } else if (value instanceof Date) {
                    Date date = (Date) value;
                    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                    textValue = sdf.format(date);
                } else if (value instanceof Timestamp) {
                    Timestamp date = (Timestamp) value;
                    SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                    textValue = sdf.format(date);
                } else {
                    textValue = value.toString();
                }
                cell.setCellValue(textValue);
            }
        }
        try {
            workbook.write(out);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出Excel的方法
     *
     * @param title   excel中的sheet名称
     * @param headers 表头
     * @param result  结果集
     * @param out     输出流
     * @param pattern 时间格式
     * @throws Exception
     */
    public void exportExcel(String title, String[] headers, String[] columns, Collection<T> result, OutputStream out, String pattern) throws Exception {
        // 声明一个工作薄   
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格   
        HSSFSheet sheet = workbook.createSheet(title);

        HSSFRow row = sheet.createRow(1);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell((short) i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        // 遍历集合数据，产生数据行
        if (result != null) {
            int index = 2;
            for (T t : result) {
                //  Field[] fields = t.getClass().getDeclaredFields();
                row = sheet.createRow(index);
                index++;
                for (short i = 0; i < columns.length; i++) {
                    HSSFCell cell = row.createCell(i);
//                   Field field = fields[i]; 
//                   String fieldName = field.getName(); 
                    String fieldName = columns[i];
                    String getMethodName = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    Class tCls = t.getClass();
                    Method getMethod = tCls.getMethod(getMethodName, new Class[]{});
                    // getMethod.getReturnType().isInstance(obj);
                    Object value = getMethod.invoke(t, new Class[]{});
                    String textValue = null;
                    if (value == null) {
                        textValue = "";
                    } else if (value instanceof Date) {
                        Date date = (Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        textValue = sdf.format(date);
                    } else if (value instanceof Timestamp) {
                        Timestamp date = (Timestamp) value;
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        textValue = sdf.format(date);
                    } else {
                        //其它数据类型都当作字符串简单处理
                        textValue = value.toString();
                    }

                    if (textValue != null) {
                        Pattern p = Pattern.compile("^//d+(//.//d+)?$");
                        Matcher matcher = p.matcher(textValue);
                        if (matcher.matches()) {
                            //是数字当作double处理 
                            cell.setCellValue(Double.parseDouble(textValue));
                        } else {
                            HSSFRichTextString richString = new HSSFRichTextString(textValue);
                            cell.setCellValue(richString);
                        }
                    }
                }
            }
        }
        workbook.write(out);
    }

    /**
     * 导出Excel的方法
     *
     * @param title   excel中的sheet名称
     * @param headers 表头
     * @param result  结果集 (List<Map<String,String>>)
     * @param out     输出流
     * @param pattern 时间格式
     * @throws Exception
     */
    public void exportExcel(String title, String[] headers, String[] columns, List<Map<String, Object>> result, OutputStream out, String pattern) throws Exception {
        // 声明一个工作薄
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 生成一个表格  
        HSSFSheet sheet = workbook.createSheet(title);

        HSSFRow row = sheet.createRow(1);
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell((short) i);
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);
            cell.setCellValue(text);
        }
        // 遍历集合数据，产生数据行
        if (result != null) {
            int index = 2;
            for (Map<String, Object> map : result) {
                row = sheet.createRow(index);
                index++;
                for (short i = 0; i < columns.length; i++) {
                    HSSFCell cell = row.createCell(i);
                    String fieldName = columns[i];
                    Object value = map.get(fieldName);
                    String textValue = null;
                    if (value == null) {
                        textValue = "";
                    } else if (value instanceof Date) {
                        Date date = (Date) value;
                        SimpleDateFormat sdf = new SimpleDateFormat(pattern);
                        textValue = sdf.format(date);
                    } else {
                        //其它数据类型都当作字符串简单处理
                        textValue = value.toString();
                    }

                    if (textValue != null) {
                        Pattern p = Pattern.compile("^//d+(//.//d+)?$");
                        Matcher matcher = p.matcher(textValue);
                        if (matcher.matches()) {
                            //是数字当作double处理 
                            cell.setCellValue(Double.parseDouble(textValue));
                        } else {
                            HSSFRichTextString richString = new HSSFRichTextString(textValue);
                            cell.setCellValue(richString);
                        }
                    }
                }
            }
        }
        workbook.write(out);
    }
}